﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for DeThiDAL
/// </summary>
public class DeThiDAL
{
    public static object IsNull(object checkValue, object replaceValue)
    {
        if (checkValue.Equals(System.DBNull.Value) || checkValue == null)
            return replaceValue;
        return checkValue;
    }

    public static List<DeThiINFO> listAll()
    {
        string sql = "select * from DeThi";
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        return SQLHelper.SetListObjects<DeThiINFO>(dr);
    }

    public static DataTable listByCauHoi(int MaCauHoi)
    {
        string sql = "select ctdt.MaDe,ch.* from ChiTietDeThi as ctdt Inner Join CauHoi as ch on ctdt.MaCH = ch.MaCH Where ctdt.MaDe = " + MaCauHoi;
        return SQLHelper.ExecuteDataTable(Conn.ConnectionString, sql, CommandType.Text);
    }

    public static DataTable DataTable()
    {
        string sql = "SELECT * FROM DeThi";
        return SQLHelper.ExecuteDataTable(Conn.ConnectionString, sql, CommandType.Text);
    }


    public static DeThiINFO GetInfo(int made)
    {
        string sql = "select * from DeThi where MaDe =" + made + "";
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        return SQLHelper.SetObject<DeThiINFO>(dr);
    }

    public static int Insert(DeThiINFO obj)
    {
        obj.MaDe = GetMa();
        obj.MaDe = obj.MaDe + 1;
        string sql = "INSERT INTO DeThi (MaDe,TenDe,SoCauHoi,ThangDiem,ChuThich,ThoiGianThi) VALUES ";
        sql+="(" + obj.MaDe + ",N'" + obj.TenDe + "',"+obj.SoCauHoi+","+obj.ThangDiem+",N'"+obj.ChuThich+"',"+obj.ThoiGianThi+")";
        return SQLHelper.ExecuteNonQuery(Conn.ConnectionString, CommandType.Text, sql, null);
    }

    public static int Update(DeThiINFO obj)
    {
        string sql = "Update DeThi SET TenDe=N'" + obj.TenDe + "',SoCauHoi=" + obj.SoCauHoi + ",";
        sql += "ThangDiem=" + obj.ThangDiem + ",ChuThich = N'" + obj.ChuThich + "', ThoiGianThi = " + obj.ThoiGianThi;
        sql += " Where MaDe=" + obj.MaDe;
        return SQLHelper.ExecuteNonQuery(Conn.ConnectionString, CommandType.Text, sql, null);
    }

    public static int Update(DeThiINFO obj, SqlTransaction trans)
    {
        string sql = "Update DeThi SET TenDe=N'" + obj.TenDe + "',SoCauHoi=" + obj.SoCauHoi + ",";
        sql += "ThangDiem=" + obj.ThangDiem + ",ChuThich = N'" + obj.ChuThich + "', ThoiGianThi = " + obj.ThoiGianThi;
        sql += " Where MaDe=" + obj.MaDe;
        return SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql, null);
    }

    public static int Delete(int made)
    {
        string sql = "Delete from DeThi where MaDe =" + made + "";
        return SQLHelper.ExecuteNonQuery(Conn.ConnectionString, CommandType.Text, sql, null);
    }

    public static int GetMa()
    {
        string sql = "SELECT ISNULL(MAX(MaDe),0) FROM DeThi";
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        int ma = 0;
        if (dr.Read())
        {
            ma = int.Parse(dr[0].ToString());
        }
        dr.Close();
        return ma;
    }

    public static int ThemCauHoiNhanh(DeThiINFO mDeThi, List<CauHoiINFO> dsCauHoi)
    {
        SqlConnection conn = null;
        SqlTransaction trans = null;
        try
        {
            conn = new SqlConnection(Conn.ConnectionString);
            if (conn.State != ConnectionState.Open)
                conn.Open();
            trans = conn.BeginTransaction();
            if (Update(mDeThi, trans) <= 0)
            {
                trans.Rollback();
                conn.Close();
                return 0;
            }
            if (ChiTietDeThiDAL.ThemCauHoiNhanh(mDeThi.MaDe, dsCauHoi, trans) <= 0) {
                trans.Rollback();
                conn.Close();
                return 0;
            }
            trans.Commit();
            conn.Close();
            return 1;
        }
        catch (Exception)
        {
            trans.Rollback();
            conn.Close();
            return 0;
        }
    }
}